Projekt SMIPEGS

Projekt Bazy Danych SMIPEGS

<img src=“logo.png” style=“width: 80%; margin:10%”/> <div style=“display: flex; justify-content: center”> <div style=“text-align: center; font-size:20pt; line-height:25pt; width: 70%”>Cyberbiezpieczeństwo Grupa Labolatoryjna 7 - Projekt Bazy Danych Semestr 1</div> </div> <br> <div style=“text-align: center; font-size:15pt”>Autorzy:</div> <div style=“text-align: center”>Szkutnik Kamil, Ścibior Kacper</div> <div style=“page-break-after: always;”></div>

Chapter 1
Spis treści

<div style=“page-break-after: always;”></div>

System Monitorowania Interakcji Pośród Emerytalnej Grupy Społecznej (SMIPEGS Lublin).

Emeryci mogą mieć problem w dowiadywaniu się o zmianach w ich najbliższym otoczeniu. Dzieje się tak ponieważ członkowie ich rodziny opuszczają swój dom rodzinny, a znajomi przebywają głównie w swoich domostwach, smutne.

Wierzymy, iż nasz SMIPEGS Lublin pomoże w bycie na bierząco z najbliższym środowiskiem co jest trudniejsze z wiekiem. Każdy z nas się kiedyś znajdzie, więc już dziś myślmy o naszej niedalekiej przyszłości, bo kiedyś my sami staniemy się emerytami. Memento mori.

Portal społecznościowy dla emerytów wiary chrześcijańskiej, z którego również mogą korzystać użytkownicy nie podzielający tej wiary. Portal składa się z dwóch części:

  • Główna Tablica ogłoszeniowa (o indeksie 0) i prywatne tablice ogłoszeniowe użytkowników na które użytkownicy o odpowiednich uprawnieniach mogą wstawiać ogłoszenia zawierające tekst i obrazki. Znajduje się tam również lista członków danej tablicy z której można wejść na opis danego użytkownika zawierający:

    • Ulubione modlitwy.

    • Parafie i ich proboszcza na oddzielnej podstronie.

  • Zakładke “profil użytkownika” na której użytkownik może zobaczyć jak widzą go inni.

  • Zakładke “rodzina” na której użytkownicy którzy są ze sobą w rodzinie mogą zobaczyć podstawowe dane osobowe (takie jak aktualny adres) osób z którymi są spokrewnieni.

<div style=“page-break-after: always;”></div>
  1. Użytkownicy nie mają możliwości wpływania na zawartość bazy danych, jedynie mogą przeglądać jej zawartość.

  2. Nad zawartością bazy czuwają admini, nad tablicami użytkownicy o odpowiednich uprawnieniach, którzy nadal nie mają siły sprawczej.

  3. Ich zwierzchnikami są odpowiedni admini z którymi mają możliwość bezpośredniej komunikacji.

  4. Wyżej w hierachi uprzywilejowanych użytkowników są ci, którzy mają wyższe uprawnienia na tablicy głównej.

  5. Admini zawsze mają prawo odmówić uprzywilejowanym użytkownikom.

  6. Wśród adminów panuje czteropodział władzy.

Admin do którego użytkownicy o uprawnieniu “kreator postów” wysyłają swoje posty, aby mógł je wstawić do odpowiedniej tablicy.

Użytkownicy o uprawnieniu “moderator postów” zgłaszają do niego zażalenia jeżeli dane ogłoszenie ujmuje ludzkiej godności lub jest niezgodne z porządkiem pubicznym.

Użytkownicy o uprawnieniu “zarządzanie użytkownikami” przekazują adminowi kierownik kogo trzeba dodać lub usunąć z danej tablicy oraz jakie uprawnienia powinien mieć użytkownik.

Admin mający całkowitą władzę nad bazą danych.

Nasza baza danych stoi na serwerze z systemem operacyjnym Debian. System do tworzenia kopii zapasowej jedynie działa na systeach z rodziny GNU/Linux.

Matuzal, syn Henocha, był człowiekiem któremu Bóg dał 969 lat życia i zaszczyt bycia jednym z przodków wszystkich ludzi po potopie. Tym mianem w naszej społeczności nazywamy użytkowników którzy dożyli 90 godnych lat. Są chlubą naszego systemu SMIPEGS Lublin.

<div style=“page-break-after: always;”></div>

W dawnych wierzeniach słowiańskim była demonem narodzonym z duszy grzesznika, a jeżeli ktoś był kierowany w życiu złością, to mógł się nią stać i za życia. Tak nazywamy w naszej społeczności użytkowników usuniętych z tablicy głównej z powodu ich udręczających zachowań w obrębach naszego systemu SMIPEGS.

  • uzytkownik

  • dane_uzytkownika

  • modlitwa

  • parafia

  • adres

  • rodzina

  • pokrewienstwo

  • proboszcz

  • opis_uzytkownika

  • tablica_ogloszeniowa

  • ogłoszenie

  • uprawnienie

  • obraz

  • tablica_ogloszeniowa_uzytkownik

Jeżeli nie zostało napisane inaczej, to domyślne wartości dla każdego atrybutu to:

  • unsigned (przy varcharze nie można ustawić unsigned)

  • not null

Wszystkie id mają unique.

Wszystkie id są autoinkrementowane.

Boolowski typ danych jest reprezentowany przez tinyint(1).

<div style=“page-break-after: always;”></div>

hasła powinny byc szyfrowane ale to zagadnienie wykracza poza naszą obecną wiedze.

AtrybutTypOgraniczenia / opis
idintklucz główny
loginvarchar(128)unique, mozliwy NULL, DEFAULT ‘uzytkownik’
haslovarchar(64)mozliwy NULL, DEFAULT ‘uzytkownik’

użytkownik o id == 1 to uzytkownik usuniety

Wartosc NULL jest nam potrzebna aby nikt nie mógł sie zalogowac na usunietego użytkowika.

AtrybutTypOgraniczenia / opis
idintklucz główny
uzytkownik idklucz obcy
imievarchar(64)
nazwiskovarchar(64)
numer_telefonuvarchar(16)możliwy NULL
data_urodzeniadate
data_smiercidatemożliwy NULL
adres_idklucz obcy, możliwy NULL
użytkownik_idklucz obcy
AtrybutTypOgraniczenia / opis
idintklucz główny
uzytkownik_idklucz obcy
plecchar(1)możliwy NULL
pseudonimvarchar(64)możliwy NULL
opisvarchar(1024)możliwy NULL
parafia_idklucz obcy, możliwy NULL
rodzina_idklucz obcy, DEFAULT ‘1’
zdjecie_profilowe_idklucz obcy, DEFAULT ‘1’
ulubiona\modlitwa_idklucz obcy, możliwy NULL
AtrybutTypOgraniczenia / opis
idsmallint(255)klucz główny
nazwavarchar(128)możliwy NULL
trescvarchar(2048)
efektvarchar(128)możliwy NULL
<div style=“page-break-after: always;”></div>
AtrybutTypOgraniczenia / opis
idintklucz główny
rejonvarchar(64)
kod_pocztowysmallint(3)zerofill
ulicavarchar(64)
numer_budynkusmall int(255)
numer_mieszkaniasmall int(255)możliwy NULL

W kodzie pocztowym nie trzymamy 20 z przodu tylko same liczby ponieważ zakładamy, że wszyscy użytkownicy sa z Lublina

AtrybutTypOgraniczenia / opis
idintklucz główny
nazwavarchar(128)
opisvarchar(1024)możliwy NULL

id == 0 to rodzina “Nieznana”

<div style=“page-break-after: always;”></div>

Użytkownik zgłasza swoją relacje z innym użytkownikiem, relacje nie są symetryczne ponieważ drugi użytkownik nie musi ją uznawać, co nie jest problemem gdyż są one czysto informacyjne.

AtrybutTypOgraniczenia / opis
idintklucz główny
typ_relacjienum(‘mama’, ‘ojciec’, ‘córka’, ‘syn’, ‘siostra’, ‘brat’, ‘ciotka’, ‘wujek’, ‘siostrzenica’, ‘bratanica’, ‘siostrzeniec’, ‘bratanek’, ‘kuzyn’, ‘kuzynka’, ‘babcia’, ‘dziadek’, ‘wnuczka’, ‘wnuk’, ‘ojczym’, ‘macocha’, ‘pasierb’, ‘pasierbica’, ‘szwagier’, ‘szwagierka’, ‘teść’, ‘teściowa’, ‘zięć’, ‘synowa’, ‘mąż’, ‘żona’)
widzi_dane_osobowebool
uzytkownik_idklucz obcy
spokrewniony_uzytkownik_idklucz obcy
AtrybutTypOgraniczenia / opis
idtinyint(255)klucz główny
imievarchar(64)
nazwiskovarchar(64)
AtrybutTypOgraniczenia / opis
idsmallint(255)klucz główny
nazwavarchar(256)unique
proboszcz_idklucz obcy
<div style=“page-break-after: always;”></div>

id == 1 to tablica glowna, kazdy uzytkownik jest tam automatycznie dodawany przez trigger

AtrybutTypOgraniczenia / opis
idsmallint(255)klucz główny
nazwavarchar(256)
opisvarchar(2048)możliwy NULL
AtrybutTypOgraniczenia / opis
idintklucz główny
tytulvarchar(128)
data_wstawieniadate
trescvarchar(512)
autor_id (emeryt_id)klucz obcy
tablica_ogloszeniowa_idklucz obcy
obrazek_idklucz obcy, możliwy NULL
archiwalnybool
<div style=“page-break-after: always;”></div>

obrazek o id 1 to domyślne zdjęcie profilowe użytkownika

AtrybutTypOgraniczenia / opis
idintklucz glówny
tekst_alternatywnyvarchar(128)możliwy NULL
AtrybutTypOgraniczenia / opis
idintklucz glówny
rolaENUM(‘zarządzanie użytkownikami’, ‘kreator postów’, ‘moderator postów’, ‘obserwator postów’)
tablica_ogloszeniowa_idklucz obcy
uzytkownik_idklucz obcy
AtrybutTypOgraniczenia / opis
idintklucz glówny
uzytkownik_idklucz obcy
tablica_ogloszeniowa_idklucz obcy
<div style=“page-break-after: always;”></div>

(I) – relacja identyfikująca (NI) – relacja nie-identyfikująca

Encja ARelacjaEncja BOpis
uzytkownik1:1 (NI)dane_uzytkownika
opis_uzytkownika1:1 (NI)uzytkownik
modlitwa1:N (NI)opis_uzytkownikaulubiona_modlitwa_id
parafia1:N (NI)opis_uzytkownika
parafia1:1 (I)proboszcz
adres1:1 (NI)dane_uzytkownika
rodzina1:N (I)opis_uzytkownika
uzytkownik1:N (I)pokrewienstwotabela pośrednia
pokrewienstwoN:1 (I)uzytkownikspokrewiony_uzytkownik_id
tablica_ogloszeniowa1:N (I)tablica_ogloszeniowa_uzytkownik
tablica_ogloszeniowa_uzytkownikN:1 (I)uzytkownik
ogloszenieN:1 (I)tablica
ogloszenieN:1 (I)uzytkownikautor_id
tablica_ogloszeniowa1:N (I)uprawnienietabela pośrednia
uprawnienieN:1 (I)uzytkownik
obrazek1:1 (NI)opis_uzytkownikazdjecie_profilowe_id
obrazek1:1 (NI)ogloszenie
<div style=“page-break-after: always;”></div>

Tabele w naszej bazie danych są większości ściśle ze sobą powiązane, co wymagało od nasstworzenia wyspecjalizowanego frameworku do generacji danych w celach testowaia poprawności działania bazy danych. Poniżej opiszemy jak z niego korzystać.

Framework jest napisany w języku Python. Do wygenrowania domyślnego schematu wymagana jest biblioteka “faker”. Aby ją zainstalować należy wpisać w konsoli:

pip install faker

Z konsoli należy wejść do folderu do_importu/sztuczne_dane/ i uruchomić komendę:

python smipegs_fake_data_generator.py

Na początku trzeba zdefiniować wszystkie tabele w pliku scripts\tables_internal_data.py i ich kolejność generacji która odpowiada kolejności w której je definiujem.

tables = {
    "nazwa_tablicy_ktora_wygeneruje_sie_jako_pierwsza": {
        "column": [
            "nazwa_kolumny_pierwszej",
            "nazwa_kolumny_drugiej"
        ],
        "data": []
    },
  
    "nazwa_tablicy_ktora_wygeneruje_sie_jako_druga": {
        "column": [
            "nazwa_kolumny_pierwszej",
            "nazwa_kolumny_drugiej"
        ],
        "data": []
    },
}

<div style=“page-break-after: always;”></div>

W pliku konfiguracyjnym config.py możemy zdefiniować paramtry generacji schematu aby później móc je łatwo zmienić.

class config:
    class nazwa_tablicy1_definition:
        parametr = "wartość parametru"
        number_of_rows = 100
    class nazwa_tablicy2_definition:
        parametr = "wartość parametru"

    nazwa_tablicy1 = nazwa_tablicy1_definition()
    nazwa_tablicy2 = nazwa_tablicy2_definition()

Schemat generowania edytujemy w pliku generation_shema.py. Schemat dla danej tabeli umieszczamy w case.

case "nazwa_tablicy1":
    # Generowanie kolumn których dane są niezależne od siebie
    def nazwa_kolumny1():
        # Kod do generacji 
        return "Wygenerowana wartość"
    def nazwa_kolumny3():
        # Kod do generacji 
        return "Wygenerowana wartość"

    # Wysyłanie wygenerowanych tabel
    row_data_to_return = generate_table_row_data(
        generation_config.nazwa_tablicy1.number_of_rows,
        nazwa_kolumny1, # Indeks 0
        # kolumna której dane generujemy w dalszej części
        column_to_replace, # Indeks 1
        nazwa_kolumny3 # Indeks 2
    )

    # Generowanie kolumn których dane są zależne od siebie
    def generate_nazwa_kolumny2_po_angielsku():
        nazwa_tego_co_generujemy_po_angielsku_w_liczbie_mnogiej = []
        # np. logins = []
        for i in range(generation_config.nazwa_tablicy2.number_of_rows):
            current_nazwa_tego_co_generujemy_po_angielsku = None
            # operacje na current_nazwa_tego_co_generujemy_po_angielsku

            nazwa_tego_co_generujemy_po_angielsku_w_liczbie_mnogiej.append(current_nazwa_tego_co_generujemy_po_angielsku)

        update_row_with_column_data(
            1, # Indeks kolumny który wysyłamy do tabel
            nazwa_tego_co_generujemy_po_angielsku_w_liczbie_mnogiej
        )

    # Generowanie kolumn dla później tabeli, gdy to co generujemy w aktualnej tabeli wpływa na tą generowaną później

    nazwa_tego_co_generujemy_do_pozniej_generowanej_tabeli_po_angielsku_w_liczbie_mnogiej = []
    for row_index in range(generation_config.nazwa_kolumny_do_ktorej_przekazemy_te_dane.number_of_rows):

    def generate_nazwa_kolumny_pozniej_generowanej_tabeli_po_angielsku(permissions):
        generate_data_for_later_table(
            'nazwa_pozniej_generowanej_tabeli',
            0, # Indeks kolumny
            nazwa_tego_co_generujemy_do_pozniej_generowanej_tabeli_po_angielsku_w_liczbie_mnogiej
        )
  
    generate_nazwa_kolumny2_po_angielsku()
    generate_nazwa_kolumny_pozniej_generowanej_tabeli_po_angielsku()

case "nazwa_tabeli2":
    row_data_to_return = generate_table_row_data(
        # get_already_generated_table(nazwa_tabeli)[indeks_kolumny] oczywiście można używać w bardziej zaawansowany sposób od tego
        len(get_already_generated_table(nazwa_tabeli2)[0]),
        column_to_replace,
    )

    def nazwa_kolumny1():
        update_row_with_column_data(
            0,
            get_already_generated_column_data(nazwa_kolumny1, 0)
        )

    nazwa_kolumny1()

<div style=“page-break-after: always;”></div>

Wyświetlanie tablic ogłoszeń do których należy użytkownik o loginie “adam_tester”

SELECT tablica_ogloszeniowa.id, tablica_ogloszeniowa.nazwa
FROM tablica_ogloszeniowa
JOIN tablica_ogloszeniowa_uzytkownik ON tablica_ogloszeniowa_uzytkownik.tablica_ogloszeniowa_id = tablica_ogloszeniowa.id
JOIN uzytkownik ON uzytkownik.id = tablica_ogloszeniowa_uzytkownik.uzytkownik_id
WHERE uzytkownik.login = "adam_tester";

Wyświetlanie tytułu i opisu tablicy od id 12 do której należy użytkownik o loginie “adam_tester” (trzeba sprawdzać login bo id tablicy przechowywane jest w url)

SELECT tablica_ogloszeniowa.nazwa, tablica_ogloszeniowa.opis FROM tablica_ogloszeniowa JOIN tablica_ogloszeniowa_uzytkownik ON tablica_ogloszeniowa_uzytkownik.tablica_ogloszeniowa_id = tablica_ogloszeniowa.id JOIN uzytkownik ON uzytkownik.id = tablica_ogloszeniowa_uzytkownik.uzytkownik_id WHERE tablica_ogloszeniowa_id = "12" and uzytkownik.login = "adam_tester";

Wyświetlanie tytulu, opisu i pseudonimu autora ogłoszeń z tablicy od id 12 do której należy użytkownik o loginie “adam_tester”

SELECT ogloszenie.id, ogloszenie.tytul, opis_uzytkownika.pseudonim
FROM ogloszenie JOIN tablica_ogloszeniowa ON ogloszenie.tablica_ogloszeniowa_id = tablica_ogloszeniowa.id
JOIN tablica_ogloszeniowa_uzytkownik ON tablica_ogloszeniowa_uzytkownik.tablica_ogloszeniowa_id = tablica_ogloszeniowa.id
JOIN uzytkownik ON uzytkownik.id = tablica_ogloszeniowa_uzytkownik.uzytkownik_id
JOIN opis_uzytkownika ON opis_uzytkownika.uzytkownik_id = ogloszenie.autor_id
WHERE ogloszenie.tablica_ogloszeniowa_id = 12 and uzytkownik.login = "adam_tester" GROUP BY ogloszenie.id;

Dane użytkownika o loginie “adam_tester”

SELECT 
opis_uzytkownika.pseudonim, 
opis_uzytkownika.plec, 
opis_uzytkownika.opis,
dane_uzytkownika.imie,
dane_uzytkownika.nazwisko,
dane_uzytkownika.numer_telefonu,
dane_uzytkownika.data_urodzenia,
adres.rejon,
adres.kod_pocztowy,
adres.ulica,
adres.numer_budynku,
adres.numer_mieszkania
FROM opis_uzytkownika 
JOIN uzytkownik ON uzytkownik.id = opis_uzytkownika.uzytkownik_id
LEFT JOIN dane_uzytkownika ON dane_uzytkownika.uzytkownik_id = uzytkownik.id
LEFT JOIN adres ON adres.id = dane_uzytkownika.adres_id
WHERE uzytkownik.login = "adam_tester"

Relacje rodzinne użytkownika o loginie “adam_tester”

SELECT 
pokrewienstwo.typ_relacji,
sygnatura.imie_pseudonim_nazwisko,
wiek.wiek
FROM pokrewienstwo
JOIN uzytkownik ON uzytkownik.id = pokrewienstwo.uzytkownik_id
JOIN sygnatura ON sygnatura.uzytkownik_id = pokrewienstwo.spokrewniony_uzytkownik_id
JOIN dane_uzytkownika ON dane_uzytkownika.uzytkownik_id = pokrewienstwo.spokrewniony_uzytkownik_id
JOIN wiek ON wiek.dane_uzytkownika_id = dane_uzytkownika.id
WHERE uzytkownik.login = "adam_tester"

SELECT ou.plec, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 0) AS procent
FROM opis_uzytkownika ou
GROUP BY ou.plec;

SELECT u.id AS uzytkownik_id,ou.pseudonim,a.rejon
FROM uzytkownik u
JOIN opis_uzytkownika ou ON ou.uzytkownik_id = u.id
JOIN dane_uzytkownika du ON du.uzytkownik_id = u.id
JOIN adres a ON a.id = du.adres_id
WHERE a.rejon = 'Rury'

<div style=“page-break-after: always;”></div>

Nie mozemy edytowac struktury bazy danych

DELETE FROM tablica_ogloszeniowa_uzytkownik 
WHERE tablica_ogloszeniowa_id = 1 AND uzytkownik_id = "dowolne id"

rozwązanie ziwązku małżeńskiego zawartego między 2 uzytkownikami

DELETE p
FROM pokrewienstwo p
JOIN opis_uzytkownika ou
  ON ou.uzytkownik_id IN (p.uzytkownik_id, p.spokrewniony_uzytkownik_id)
WHERE ou.pseudonim = 'smutnyMarian'
  AND p.typ_relacji IN ('mąż', 'żona');

ustawianie małżenstwa dla 2 uzytkowników

INSERT INTO pokrewienstwo (typ_relacji, spokrewniony_uzytkownik_id, uzytkownik_id)
SELECT 'żona' typ_relacji, c1.uzytkownik_id, c2.uzytkownik_id
FROM
(SELECT uzytkownik_id FROM opis_uzytkownika WHERE pseudonim = 'mariolkaRolka') c1,
(SELECT uzytkownik_id FROM opis_uzytkownika WHERE pseudonim = 'wesołyMarian') c2;
INSERT INTO pokrewienstwo (typ_relacji, spokrewniony_uzytkownik_id, uzytkownik_id)
SELECT 'mąż' typ_relacji, c1.uzytkownik_id, c2.uzytkownik_id
FROM
(SELECT uzytkownik_id FROM opis_uzytkownika WHERE pseudonim = 'wesołyMarian') c1,
(SELECT uzytkownik_id FROM opis_uzytkownika WHERE pseudonim = 'mariolkaRolka') c2;

polecenie wypisuje wszyskich nieaktywnych postów i pozwala administratorowi zadecydowac nad ich losem.

SELECT u.id AS uzytkownik_id, ou.pseudonim, pk.liczba_postow,
MAX(o.data_wstawienia) AS ostatni_post
FROM uprawnienie up
JOIN uzytkownik u ON u.id = up.uzytkownik_id
JOIN opis_uzytkownika ou ON ou.uzytkownik_id = u.id
JOIN plodnosc_kreatorow_postow pk ON pk.pseudonim = ou.pseudonim
LEFT JOIN ogloszenie o ON o.autor_id = u.id
WHERE up.rola = 'kreator postów'
GROUP BY u.id, ou.pseudonim, pk.liczba_postow
HAVING MAX(o.data_wstawienia) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

wyswietla ile postów dodał dany uzytkownik

DROP VIEW IF EXISTS plodnosc_kreatorow_postow;
CREATE VIEW plodnosc_kreatorow_postow AS 
SELECT s.Imie_pseudonim_nazwisko, COUNT(o.id) AS liczba_postow 
FROM uzytkownik u 
LEFT JOIN sygnatura s ON s.id = u.id 
LEFT JOIN ogloszenie o ON o.autor_id = u.id
GROUP BY s.Imie_pseudonim_nazwisko
ORDER BY liczba_postow DESC;

<div style=“page-break-after: always;”></div>

wyswietla ile postów znajduje sie na danej tablicy

DROP VIEW IF EXISTS plodnosc_tablicy;
CREATE VIEW plodnosc_tablicy AS
SELECT t.id, t.nazwa, 
COUNT(DISTINCT tou.uzytkownik_id) AS liczba_uzytkownikow, 
COUNT(DISTINCT o.id) AS liczba_postow
FROM tablica_ogloszeniowa t 
LEFT JOIN tablica_ogloszeniowa_uzytkownik tou ON t.id = tou.tablica_ogloszeniowa_id 
LEFT JOIN ogloszenie o ON o.tablica_ogloszeniowa_id = t.id 
GROUP BY t.id, t.nazwa
ORDER BY liczba_postow DESC;

<div style=“page-break-after: always;”></div>

wyswietla ilu uzytkowników jest w danej parafii

DROP VIEW IF EXISTS plodnosc_parafii;
CREATE VIEW plodnosc_parafii AS
SELECT p.id, p.nazwa, COUNT(ou.id) AS liczba_wiernych
FROM parafia p
JOIN opis_uzytkownika ou ON ou.parafia_id = p.id
GROUP BY p.id, p.nazwa;

wyswietla które modlitwy najczesciej znajduja sie w opisach uzytkowników

DROP VIEW IF EXISTS pozycja_modlitwy;
CREATE VIEW pozycja_modlitwy AS 
SELECT m.id, m.nazwa, COUNT(ou.id) AS liczba_polubien
FROM modlitwa m
JOIN opis_uzytkownika ou ON ou.ulubiona_modlitwa_id = m.id
GROUP BY m.id, m.nazwa;

wyswietla które rodziny maja najwiecej członków

DROP VIEW IF EXISTS pozycja_rodziny;
CREATE VIEW pozycja_rodziny AS
SELECT r.id, r.nazwa, COUNT(ou.id) AS liczba_czlonkow
FROM rodzina r
JOIN opis_uzytkownika ou ON ou.rodzina_id = r.id
GROUP BY r.id, r.nazwa;

<div style=“page-break-after: always;”></div>

wyswietla uzytkownikow mających co namniej 90 lat

DROP VIEW IF EXISTS matuzal;
CREATE VIEW matuzal AS
SELECT u.id, s.Imie_pseudonim_nazwisko, w.wiek
FROM uzytkownik u
JOIN sygnatura s ON s.id = u.id
JOIN dane_uzytkownika du ON du.uzytkownik_id = u.id
JOIN wiek w ON w.dane_uzytkownika_id = du.id
WHERE w.wiek >= 90
ORDER BY w.wiek DESC;

<div style=“page-break-after: always;”></div>

uzytkownicy usunieci z tablicy głównej

DROP VIEW IF EXISTS zmora;
CREATE VIEW zmora AS
SELECT u.id, s.imie_pseudonim_nazwisko
FROM uzytkownik u
JOIN sygnatura s ON s.id = u.id
WHERE NOT EXISTS (
    SELECT 1 
    FROM tablica_ogloszeniowa_uzytkownik tou
    WHERE tou.uzytkownik_id = u.id AND tou.tablica_ogloszeniowa_id = 1
);


<div style=“page-break-after: always;”></div>

uzytkownicy którzy nie żyja

DROP VIEW IF EXISTS zmarly_uzytkownik;
CREATE VIEW zmarly_uzytkownik AS
SELECT u.id, s.imie_pseudonim_nazwisko, du.data_smierci
FROM uzytkownik u
JOIN sygnatura s ON s.id = u.id
JOIN dane_uzytkownika du ON du.uzytkownik_id = u.id
WHERE du.data_smierci IS NOT NULL;

<div style=“page-break-after: always;”></div>

wyswietla imie, pseudonim i nazwisko w jednej komórce

DROP VIEW IF EXISTS sygnatura;
CREATE VIEW sygnatura AS
SELECT u.id, CONCAT(COALESCE(du.imie, ''), ' "', COALESCE(ou.pseudonim, ''), '" ', COALESCE(du.nazwisko, '')) AS imie_pseudonim_nazwisko
FROM uzytkownik u
LEFT JOIN opis_uzytkownika ou ON ou.uzytkownik_id = u.id
LEFT JOIN dane_uzytkownika du ON du.uzytkownik_id = u.id;

<div style=“page-break-after: always;”></div>

wyswietla ile lat ma kazdy uzytkownik

DROP VIEW IF EXISTS wiek;
CREATE VIEW wiek AS
SELECT dane_uzytkownika.id AS dane_uzytkownika_id, 
CASE
    WHEN data_smierci IS NULL THEN TIMESTAMPDIFF(YEAR, dane_uzytkownika.data_urodzenia, CURDATE())
    ELSE TIMESTAMPDIFF(YEAR, dane_uzytkownika.data_urodzenia, dane_uzytkownika.data_smierci)
END AS wiek
FROM dane_uzytkownika;

<div style=“page-break-after: always;”></div>

wyswietla rodzina małzonka

DROP VIEW IF EXISTS rodzina_wzeniona;
CREATE VIEW rodzina_wzeniona AS 
SELECT o.rodzina_id AS rodzina_id, u.id AS uzytkownik_id
FROM uzytkownik u
JOIN pokrewienstwo p ON p.uzytkownik_id = u.id
JOIN uzytkownik wspolmalzonek ON wspolmalzonek.id = p.spokrewiniony_uzytkownik_id
JOIN opis_uzytkownika o ON o.uzytkownik_id = wspolmalzonek.id
WHERE p.typ_relacji IN ('mąż', 'żona');

<div style=“page-break-after: always;”></div>

wyswietla url obrazka

DROP VIEW IF EXISTS url_obrazka;
CREATE VIEW url_obrazka AS
SELECT o.id AS obrazek_id, CONCAT('/img/', o.id, '.jpg') AS url
FROM obrazek o;

<div style=“page-break-after: always;”></div>

wyswietla kod pocztowy uzytkownika

DROP VIEW IF EXISTS kod_pocztowy;
CREATE VIEW kod_pocztowy AS
SELECT a.id, CONCAT('20-',LEFT(a.kod_pocztowy, 3)) AS kod_pocztowy
FROM adres a;

<div style=“page-break-after: always;”></div>

Dodaje uzytkownika do tablicy głównej przy dodaniu użytkownika

CREATE TRIGGER po_wstawieniu_do_uzytkownik
AFTER INSERT ON uzytkownik
FOR EACH ROW
INSERT INTO tablica_ogloszeniowa_uzytkownik (uzytkownik_id, tablica_ogloszeniowa_id)
VALUES (NEW.id, 1);

Ustawia uzytkownikowi role obserwatora postów przy dodaniu do nowej tablicy

CREATE TRIGGER po_wstawieniu_do_tablica_ogloszeniowa_uzytkownik
AFTER INSERT ON tablica_ogloszeniowa_uzytkownik
FOR EACH ROW 
INSERT INTO uprawnienie (rola,tablica_ogloszeniowa_id,uzytkownik_id)
VALUES ('obserwator postow',NEW.tablica_ogloszeniowa_id,NEW.uzytkownik_id);

Zabieramy mu uprawnienia

CREATE TRIGGER po_usunieciu_z_tablica_ogloszeniowa_usun_uprwanienie
AFTER DELETE ON tablica_ogloszeniowa_uzytkownik
FOR EACH ROW
DELETE FROM uprawnienie
WHERE uzytkownik_id = OLD.uzytkownik_id;

Usuwamy go z tablic

CREATE TRIGGER przed_usunieciem_uzytkownik_usun_z_tablice
BEFORE DELETE ON uzytkownik
FOR EACH ROW
DELETE FROM tablica_ogloszeniowa_uzytkownik
WHERE uzytkownik_id = OLD.id;

<div style=“page-break-after: always;”></div>

Usuwamy ustawiony przez niego opis

CREATE TRIGGER przed_usunieciem_uzytkownik_usun_opis
BEFORE DELETE ON uzytkownik
FOR EACH ROW
DELETE FROM opis_uzytkownika
WHERE uzytkownik_id = OLD.id;

Usuwamy wypełnione przez niego dane osobowe

CREATE TRIGGER przed_usunieciem_uzytkownik_usun_dane
BEFORE DELETE ON uzytkownik
FOR EACH ROW
DELETE FROM dane_uzytkownika
WHERE uzytkownik_id = OLD.id;

Usuwamy mu powiazania z innymi uzytkownikami

CREATE TRIGGER przed_usunieciem_uzytkownik_usun_pokrewienstwo
BEFORE DELETE ON uzytkownik
FOR EACH ROW
DELETE FROM pokrewienstwo 
WHERE uzytkownik_id = OLD.id OR spokrewniony_uzytkownik_id = OLD.id;

Posty które stworzył sa przypisaywane autorowi o id = 1 ‘usuniety uzytkownik’

CREATE TRIGGER przed_usunieciem_uzytkownika_usun_posty
BEFORE DELETE ON uzytkownik
FOR EACH ROW
UPDATE ogloszenie 
SET autor_id = 1
WHERE autor_id = OLD.id;

Usuamy adres zamieszkania z bazy, tylko wtedy jezeli nikt inny pod nim nie mieszka

CREATE TRIGGER po_usunieciu_danych_usun_adres
AFTER DELETE ON dane_uzytkownika
FOR EACH ROW
    IF OLD.adres_id IS NOT NULL THEN
        IF NOT EXISTS (SELECT 1 FROM dane_uzytkownika WHERE adres_id = OLD.adres_id) THEN
            DELETE FROM adres WHERE id = OLD.adres_id;
        END IF;
    END IF;

Nasze wyzwalacze działaja wspólnie ze soba, gdy dodajemy uzytkownika:

To automatycznie zostanie dodany do tablicy głównej:

Oraz zostanie mu przypisana rola ‘obserwtor postów’

<div style=“page-break-after: always;”></div>

Gdy postanowimy usunac uzytkownika

To system posprzata i usunie wszystkie dane powiazane z uzytkownikiem

Posty uzytkownika zostały przypisane autorowi o id = 1

Pozostał jedynie adres uzytkownika ponieważ w bazie znajdowal sie inny uzytkownik który mieszkal pod tym samym adresem

<div style=“page-break-after: always;”></div>

Nasz system SMIPEGS potrzebuje aby jednej procedury, gdyż inne czynności są dosyć łatwe w napisaniu zwykłym zapytaniem SQL.

Pozwala admistratorowi podejrzec przedawnione posty na podstawie daty wstawienia z pominieciem postów oznaczonych jako ‘do archiwizacji’. Procedura pozwala na wyszukanie postów starszych niz x lat lub postów stworzonych do konkretnej daty. Mozna tez podejrzec kolumny do usuniecia jesli nie ustawimy parametru usunac na ‘true’.

DROP PROCEDURE IF EXISTS usun_stare_ogloszenia;

DELIMITER $$

CREATE PROCEDURE usun_stare_ogloszenia(
    IN starsze_niz INT,
    IN do_kiedy DATE,
    IN usunac BOOLEAN
)
BEGIN
    DECLARE data_graniczna DATE;
    IF usunac IS NULL THEN
    	SET usunac = 0;
	END IF;

    IF (starsze_niz IS NOT NULL AND starsze_niz > 0)
        AND (do_kiedy IS NOT NULL AND do_kiedy <> '0000-00-00') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Nie można podać obu parametrów jednocześnie';

    ELSEIF starsze_niz IS NOT NULL AND starsze_niz > 0 THEN
        SET data_graniczna = DATE_SUB(CURDATE(), INTERVAL starsze_niz YEAR);

    ELSEIF do_kiedy IS NOT NULL AND do_kiedy <> '0000-00-00' THEN
        SET data_graniczna = do_kiedy;

    ELSE
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Nie podano poprawnego parametru';
    END IF;

    SELECT *
    FROM ogloszenie
    WHERE data_wstawienia < data_graniczna
        AND (archiwalny IS NULL OR archiwalny = 0);
	IF (usunac) THEN
    DELETE
    FROM ogloszenie
    WHERE data_wstawienia < data_graniczna
        AND (archiwalny IS NULL OR archiwalny = 0);
	END IF;
END$$

DELIMITER ;

Wyszukujemy procedure w pasku bocznym, klikamy przycisk execute, w parametrach podajemy tylko wartosc parametru ‘starsze_niz’ = 1 nastepnie naciskamy przycisk Go.

Procedura pokarze ogloszenia starsze niz 1 rok, nie usunie ich ponieważ nie zmieniamy wartosci paramatru ‘usunac’.

<div style=“page-break-after: always;”></div>

Kopia zapasowa jest tworzona automatycznie o godzinie 2:30

##!/bin/bash

mkdir -p "$BACKUP_PATH"

## Konfiguracja
USER="root"
PASSWORD=""   
DATABASE="smipegs_lublin"   
BACKUP_PATH="/home/server/backups"
LOG_FILE="/home/server/logi.log"
DATE=$(date +%Y-%m-%d_%H%M%S)

## Wykonanie kopii
mysqldump -root -p$PASSWORD $DATABASE > $BACKUP_PATH/$DATABASE-$DATE.sql

## Logi
echo "$DATE: Wykonanie kopii zapasowej." >> "LOG_FILE"

nadajemy prawo do wykonywania i dodajemy wpis do crona aby automatycznie sie wykonywal

sudo chmod +x skrypt_do_automatycznej_kopii.sh
crontab -e

wewnątrz dodajemy linie:

30 2 * * * /home/server/scripts/skrypt_do_automatycznej_kopii.sh

<div style=“page-break-after: always;”></div>
<div style=“page-break-after: always;”></div>

nie musimy wybierac nowej pustej bazy danych, skrypt sam utworzy baze o nazwie smipegs_lublin

Otwieramy projekt zawierajacy baze danych lokalizacja pliku: do_importu\projekt_bazy_babaa_kabaaba.mwb

W górnym rogu klikamy w File i wybieramy opcje Export --> Forward Engineer SQL Script

W panelu wybieramy opcje ukazane na zrzucie ekranu i klikamy przycisk Next

<div style=“page-break-after: always;”></div>

W kolejnym panelu odznaczamy importowanie widoków (Views) i klikamy przycisk Next

<div style=“page-break-after: always;”></div>

W ostatnim panelu wybieramy opcje “Save to Other File” i nadpiujemy plik w tej lokalizacji do_importu\konwersja_workbench_xampp\pusta_baza_mysql.sql

<div style=“page-break-after: always;”></div>

Uruchamiany program z tej lokalizacji: do_importu\konwersja_workbench_xampp\smipegs_mysql_to_mariadb_translator.py

Nastepnie uruchamiamy kolejny proram z tej lokalizacji: do_importu\sztuczne_dane\smipegs_fake_data_generator.py

<div style=“page-break-after: always;”></div>

Na górnym panelu klikamy w zakladke import wybieramy plik do_importu/1_pusta_baza_z_triggerami.sql, odznaczamy foregin key checks a reszte opcji pozostawiamy ustawionych domyslnie.

<div style=“page-break-after: always;”></div>

Nastepnie klikamy w nowo utworzona baze danych smipegs_lublin, wchodzimy w zakładke import i importujemy plik do_importu/2_initial_data_with_generated_data.sql wczesniej odznaczajac foregin key checks.

<div style=“page-break-after: always;”></div>

Na Koniec importujemy w tej samej zakładce plik do_importu\3_uzytkownicy.sql z zachowaniem domyślych ustawień.

<div style=“page-break-after: always;”></div>

poprawna struktura danych po imporcie

uprawnienia widoków: matuzal, plodnosc_kreatorow_postow, plodnosc_parafii, plodnosc_tablicy, pozycja_modlitwy, pozycja_rodziny, zmora, zmarly_uzytkownik

uprawnienia bazy danych

uprawnienia tablicy: ogloszenie

uprawnienia tablicy: tablica_ogloszeniowa_uzytkownik, uprawnienie

uprawnienia wszystkich innych tabel i widoków